CATEGORY ONE – Navigation
& Selection in Excel
1.
Move Cell in
same Excel Sheet
|
Navigate to |
Key
Combination |
|
Top of the worksheet
(cell A1) |
CTRL + HOME |
|
End of the worksheet
(last cell containing data) |
CTRL + END |
|
End of the row |
CTRL + Right Arrow Key |
|
End of the column |
CTRL + Down Arrow Key |
|
Start of the row |
CTRL + Left Arrow Key |
|
Top of the column |
CTRL + Up Arrow Key |
2.
Move Cell to
different Excel Sheet
|
Navigate to |
Key
Combination |
|
Move to Next Sheet |
CTRL + PAGE UP |
|
Move to Previous Sheet |
CTRL + PAGE DOWN |
3.
Select Range
of Cells
Press Shift and select
desired cells.
|
Selection |
Key
stroke |
|
From any cell to the
LAST used cell |
SHIFT + CTRL + END |
|
From any cell to the
FIRST cell (A1) |
SHIFT + CTRL + HOME |
|
From Left to LAST used cell in same Column |
SHIFT + CTRL + Right Arrow Key |
|
From Top to LAST used
Cell in the Row |
SHIFT + CTRL + Down Arrow Key |
|
From Right to FIRST
used cell in same Column |
SHIFT + CTRL + Left Arrow Key |
|
From Bottom to FIRST
used Cell in the Row |
SHIFT + CTRL + Up Arrow Key |
|
Entire Column |
CTRL + SPACE BAR |
|
Entire Row |
SHIFT + SPACE BAR |
4.
Keep the top
row (for e.g. the Header Row) and / or the left column always visible even
as I scroll down or scroll to the right?
Use the Freeze Panes
feature
Position cell from which
you want pane to start, Click on the Window menu, then, click Freeze Panes.
Resultantly, the header
row would always stay visible even as you scroll down
To make column A always
visible even as you scroll to the right, select cell B1, Click on the Window
menu, then, click Freeze Panes.
To make both column A and
row 1 i.e. header row always visible even as you scroll to the right and you
scroll down, select cell B2, Click on the Window menu, then, click Freeze
Panes.
5.
Get to view
all open workbooks at the same time on the screen?
Multiple View – Arrange
Workbooks
Steps:
- Click on the Window
menu, click Arrange.
- Under Arrange, click
the option you want:
a.
Titled
b.
Horizontal
c.
Vertical
d.
Cascade
e.
To view
sheets in only the active workbook, select the Windows of active workbook
check b
-
Click OK
CATEGORY TWO - Entering Data
and Data Validation
- Make formulas easier
to read?
Name Cells or Ranges (Press
CTRL + F3)
Steps:
1.
Select the
cell or cell range
2.
Click Insert
Menu : Name : Define
3.
Define Name
4.
Click OK
Usage Hint: define A1 as
Cost and B1 as Quantity à write Cell C1= Cost * Quantity instead of C1=A1*B1
- Create shortcuts for
commonly used words or phrases?
Use the Auto Correct feature
to create shortcut for commonly used words or phrases.
Click Tools à AutoCorrect.
On the AutoCorrect Tab check
the option ‘Replace Text As You Type’.
Write the short form of the
phrase in the Replace: text box and the actual phrase in the With: text box.
Click Add
- Quickly fill a series
of values?
Use the AutoFill feature. It
Inserts a series of values or text items in a range of cells
Enter “January” in cell A1.
Drag the AutoFill handle (the small box at the lower right of the Active cell)
to copy the cell or automatically complete the series.
- Fill Blank Cells in a
list
Many times in a list,
especially so when it is imported from a database, cells down below that have
same headings or subheadings are left blank for the sake of easier
readability. For eg. See the screenshot below
However this creates a
difficulty when such lists are required to be sorted and/or subtotaled. To
fill the cells down below with the subheadings of the cells above them, do the
following:
- Select the list
- Click <Edit> à <Go
to..>.
- In the [Go To]
dialogue box that appears, click the ‘Special’ button
- In the [Special]
dialogue box, select ‘blanks’. This will highlight only the blank cells in
the list, with focus on the 1st blank cell. See figure
- In the 1st
blank cell, i.e. the cell with the focus, enter the formula =A2 (see figure)
and press CTRL+ENTER.
- As a result, the blank
cells down below get filled up with the subheadings immediately above them.
See figure
10.
Create Drop
Down Lists?
Havent you always
wanted to create those lovely drop down list boxes to enter data in lists or
forms for uniformity and standardisation? (See Figure)
These are called data
validation list boxes. Here’s how create them. In our example, let us
consider that the user has to choose a designation from Chartered Accountant,
Advocate, Managing Director, CEO, Executive and Officer. The designation is to
be entered in column D. To create the List box, select the entire column D and
click on <Data> à <Validation…>. This will activate the [Data
Validation] dialogue box. This dialogue box has three tabs viz., [Settings],
[Input Message] and [Error Alert.] The [Settings tab], prompts you for the
validation criteria. In the first list box captioned Allow, select the
option à List. Now in the third list box captioned Source, enter the
designation list, separated by comma (See Figure).
In the Input message tab,
you may enter a message like {Please Select A Designation from the List}. This
may help the person who enters data as regards what is to be entered in that
particular cell. In the Error Alert tab, you may enter a message like {Invalid
Designation, Only an item from the list is allowed!}. This message is flashed
whenever the data entry person enters a designation that is not one from the
List. If you do not specify the Error Alert Message, Excel displays its
standard Error Alert Message. That’s it! Click <OK>, and you are done. Now in
every cell of column D, a list box appears as under. You may select the
appropriate designation, by simply clicking the drop down list box arrow. For
Key Board addicts, who find it irritating to reach for the mouse every time
the designation is to be entered, just go the cell in column D, and hit the
[ALT + Down Arrow Key]
11.
Automatically
include the last row in a data validation drop down list?
The trick is to create a
dynamic named range and then use it in data validation list box. The link over
here explains in detail how this can be done:
http://www.cpearson.com/excel/named.htm
12.
Suggest what
is to be entered in a cell? (Cell comment / Data validation Input message)
You can suggest the user as
regards what is to be entered in the cell by inserting a comment in the cell.
Select the cell wherein you wish to see the message, right click and select
<Insert Comment>. Type the instruction in the comment box. That’s it. You are
done. See figure
Note that the cell comment
is displayed only when you move the cursor over the cell.
13.
Change the
User Name in the cell comment?
Instead of showing the user
name at the start of a comment, you can change to something generic, such as
"Sir" To do this Choose <Tools> à <Options>. Select the [General] tab. Delete
the existing User Name, and type a new entry. Click OKSee figure.
However, note that this
change affects the User Name in all Microsoft Office programs.
14.
Change the
shape of the Comment?
Make your worksheet a little
more interesting, by changing the default comment shape. Right-click the cell
which contains the comment. Choose <Edit Comment>. Click on the border of the
comment, to select it. On the Drawing toolbar, click the Draw button. Choose
Change AutoShape, and choose a category. Click on a shape to select it. When
finished, click outside the comment. See figure
15.
Is there an
alternative to Cell comment?
An alternative to giving
Cell comment is to give a Data Validation Input Message. Note that this
message is displayed when you tab to the cell. Select the cell in which you
want to see the message. Choose <Data> à <Data Validation>. Click on the Input
Message tab to activate it .Add a check mark to 'Show input message when cell
is selected'. Type your message heading text in the Title box. This text will
appear in bold print at the top of the message. Type your message in the Input
message box. Click OK. See figure.
16.
Get more than
256 columns and/or 65536 Rows?
Sorry, this is not possible
in Excel 2002, 2003 or XP. Check out Excel 2007 for 16,384 columns and 1048576
rows.
CATEGORY THREE – Insert,
Formatting and Copy paste
17.
Insert current
Date?
Press CTRL + ;
18.
Insert current
time?
Press SHIFT +
CTRL + ;
19.
Copy from and
paste only values from one cell to another?
Consider this example. Cell
B3 contains the Formula =B1-B2 (see figure below).
This formula evaluates to 200. This ‘200’
is the value of Cell B3. To copy and paste this Value i.e. 200,
to the adjoining cell C3.
Steps
1.
Go to cell B3, Press
CTRL + C,
2.
Go to cell C3 and click Edit Menu:
Paste Special dialogue box shall appear (See figure). 
.
3.
Select Values and
4.
Click on OK.
20.
Convert rows into columns?
Well, consider this. You have
created a sheet wherein headings like LName, FName, MName, Address1, Address2,
etc, are arranged in rows while records appear as columns besides these
headings (see Figure below).
Now, after entering around thirty to fourty
records, you realise the obvious drawbacks of this layout and wish to
rearrange the entire sheet by making the headings as columns and records below
them as rows. This is what you can do.
Steps:
1.
Select all cells, including the
heading, and copy them.
2.
Open a fresh worksheet and select
cell A1.
3.
Click on Edit Menu: Paste
Special.
4.
In the [Paste Special] dialogue box
simply check the Transpose checkbox
5.
Click OK.
21. Copy
from Excel to Word?
Consider this. You have just finished
typing out a lengthy report in Excel. At the very end, you realise that the
report has hardly any calculations, and the formatting of the report would
have been far better had the report been typed out in Word. So, you now wish
to move only the unformatted text from the spreadsheet (Report) in Excel to
Word and later on format it. This is what you can do.
Steps:
1.
Select the entire report and copy
it.
2.
Open a fresh document in Word.
3.
Click on Edit Menu: Paste Special
4.
In
the Paste Special dialogue Box, select the Paste radio button on the
left hand side and form the As: list box select the Unformatted Text
Click Ok
22.
Change case i.e. from UPPER
(CAPITAL) case to lower (small) case in a range of cells?
You need a macro to do this. Macro code to
do this is available at
http://www.mvps.org/dmcritchie/excel/proper.htm
23.
Format a cell to blink or flash when
a condition is met?
Sorry, this is not possible without a
macro. Even with a macro, it is not advisable.
24.
Automatically highlight maximum and
minimum values in a list?
This is done using conditional formatting.
Click here to know how
http://www.exceltip.com/show_tip/Excel_Format/Applying_Colors_to_Maximum/Minimum_Values_in_a_List/295.html
CATEGORY FOUR – Auto Filters and Advanced
Filters
25.
Remove duplicates / Filter
Unique Records?
You can use an Advanced Filter to extract a
list of unique items in the list.
Steps:
1.
Click on Data: Filter Advance filter
2.
In the Advanced Filter dialogue box
that opens, check out the ‘Unique records only’ option (See figure below).
26.
Filter for more than one criterion?
Use ‘Criteria range:’ in the [Advanced
Filter] dialogue box to filter a list that meets a given criteria. You can use
more than one criteria to filter a list as well.
To filter a list for records that meet ALL
the criteria, write all conditions in one row. This shall filter the list for
records that meet all the conditions. For example writing the criteria in this
way
shall filter the list for records where -
Customer = CityMall AND Product = Cakes AND
Total = greater than 500.
To filter a list for records that meet ANY
of the criteria, write conditions in different rows. This shall filter the
list for records that meet any of the conditions. For example writing the
criteria in this way
shall filter the list for records where -
Customer = CityMall OR Product = Cakes OR
Total = greater than 500.
27.
Filter for records containing text
string / Use Wildcards in Criteria?
Use wildcard characters to filter for a
text string in a cell.
The * wildcard
The asterisk (*) wildcard character
represents any number of characters in that position, including zero
characters. In this example,
any customer whose name contains "Mall"
will pass through the filter.
The ? wildcard
The question mark (?) wildcard character
represents one characters in that position. In this example
any 4-letter product that begins with ‘c’,
and ends with ‘ke’ (eg. Coke, Cake) will pass through the filter.
The ~ wildcard
The tilde (~) wildcard character lets you
search for characters that are used as wildcards. In this example,
only the product named Good*Eats, will pass
through the filter.
28.
See the Count of Filtered Records in
Status Bar?
Normally, after you have applied an
AutoFilter, the Status Bar shows a count of visible records.
Sometimes it just says, "Filter Mode."
This
can happen when your list has many formulas. There are articles in the
Microsoft KnowledgeBase that explain:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q189479
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q213886
The workaround is to go to [Options]
dialogue box in the <Tools> and in the “Calculation” tab select ‘Manual’. For
a detailed explanation of the causes and solution to the problem, check out
the links above.
29.
Sum only visible rows in a filtered
list.
To sum only visible rows which contain
data, you can use the Subtotal function in a formula in the same row as
your headings. For example,
to sum the visible entries in column D
which contain numbers, you could use this formula: =SUBTOTAL(9, D:D) .
The number ‘9’ in the first part of the formula, tells Excel to ‘Sum’ the
numbers in column D.
30.
Count visible rows in a filtered
list?
Similarly, to count the visible rows in a
filtered list, use the Subtotal formula with number ‘2’ in the first part of
the formula. For example,
The formula used in the above screenshot
counts the number of visible rows in column D.
31.
Split Contents of Cells?
Suppose you have entered the full name of a
person in a particular cell and you now want to have Surname, First Name and
Middle Name in three different cells. To do this, simply select the cell that
contains the Full Name of the person, Click <Data> à <Text to
Columns…>. This will activate the text to columns wizard (See Figure).
As a result a dialogue box captioned
[Convert Text to Columns Wizard - Step 1 of Three] shall be displayed. In step
No 1 select the First radio button captioned <Delimited>, then click
the <Next > > button. In step No. 2, from the various types of Delimiters,
select the check Box [Space]. You now see how the Full Name shall be split
into Surname, First Name and Middle Name. If you are fine with the display,
click <Next>. In step No. 3, amongst other things, you are asked to provide
the cell address from where you would like the split text to appear. That’s
it, on the main screen wizard of step Three click <Finish>. You now see the
Full Name split into Surname, First Name and Middle Name in three different
cells.
CATEGORY FIVE – Formulas and Functions
32.
My spreadsheet does not calculate at
all, what’s wrong?
Calculation is set to Manual, alter this in
Tools > Options menu.
33.
See all formulas at the same time
(formula view) in a worksheet?
The formula view is the normal method of
showing formulas in Excel.
Click on Tools Menu: Options: View: Formula
On/Off
(equivalent shortcut (toggle on/off) – CTRL
+ `)
34.
Round off a number
|
Function |
Use |
|
ROUND(number,num_digits) |
Rounds a number to
a specified number of digits |
|
ROUNDUP(number,num_digits) |
Rounds a number
up, away from 0 (zero) |
|
ROUNDDOWN(number,num_digits) |
Rounds a number
down, toward 0 (zero) |
|
MROUND(number,multiple) |
Returns a number
rounded to the desired multiple |
|
EVEN(number) |
Returns number
rounded up to the nearest even integer |
|
ODD(number) |
Returns number
rounded up to the nearest odd integer |
For eg. Round(A1,-1) rounds the number in
cell A1 to nearest 10s
35.
Calculate the difference between two
dates (e.g. calculate age in Excel)?
Use the Datediff function to do this. Where
date of birth is entered in Cell C2 and today’s date in cell D2, the following
formula will calculate the age in completed years, months and days:
=DATEDIF(C2,D2,"y") & " years, " & DATEDIF(C2,D2,"ym") & " months, "
&DATEDIF(C2,D2,"md") & " days, "
36.
Automatically write amount in words
for any number (as in a cheque)?
You need to insert a function to do this.
The macro code for this function can be downloaded for free from Microsoft’s
website at
http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 . If you
are new to Macros, VBA and functions you would want to have a look at “Getting
Started with Macros and User defined functions at”:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Also check out the the reply to question No
42 of this write up.
37.
Pick 20 random items (random
sampling) from a list of 100 (population)?
Use RAND() formula to do this. Say your
population is in column A from A1:A100. In B1:B100 enter formula =RAND(). Sort
the list by B column; top 20 rows is your selection. Press F9 for new numbers
in column B and repeat for a new selection.
38.
Get the name of a sheet in a cell
without using code?
=CELL("Filename",A1) returns the complete
file path and sheet name
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
will strip away everything but the sheet name.
Note: The file must be saved or the formula
will not work.
39.
Count cells?
|
Cells that contain
data |
COUNTA()
|
|
Cells that do not
contain data |
COUNTBLANK() |
|
Cells that contain
only numbers |
COUNT() |
|
Cells in the Range
that meet the Criteria |
COUNTIF() |
40.
On inserting a new row at the end,
how do I automatically include it in the Sum() range of cells –
If you insert a row directly above the SUM
formula, the new row would not be included in the SUM. For e.g., have a look
at the figure below.
In this case, if a new row is inserted
after row 4, Excel would continue to sum cells C1:C4, and would ignore C5. To
ensure that new rows are included in the total, you can use the OFFSET
function.
Select cell C5.
Enter the following formula:
=SUM(A1:OFFSET(C5,-1,0)) and press the Enter key to complete the entry. Insert
a row above row 5 Type a number in cell C5, and it will be included in the
total in cell C6.
41.
Combine Text from Multiple Cells?
To combine text from multiple cells into
one cell, use the & (ampersand) operator. Add a space between double quotes (“
“) to include a space in the combined text (See Figure)
CATEGORY SIX – Financial functions
42.
Calculate the effective annual
interest rate where interest is compounded?
|
EFFECT(nominal_rate,npery) |
Calculates the effective annual interest rate, given the nominal annual
interest rate and the number of compounding periods per year |
|
For
e.g. where the nominal interest rate is 5.25% p.a. and the compounding is
quaterly, the effective interest rate works out to be 5.3543%. |
Nominal_rate : is the
nominal interest rate.
Npery : is the
number of compounding periods per year. e.g. 2 if compounded semi-annually, 4
if compounded quaterly.
See figure.
43.
I am buying Government security that
pays periodic interest. How do I calculate accrued interest on it?
|
ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis) |
Calculates accrued
interest on a security that pays periodic payment. |
Issue : is the
security's issue date.
(First_interest is
the security's first interest date.)
Settlement : is the
security's settlement date.
(The
security settlement date is the date after the issue date when the security is
traded to the buyer.)
Rate : is the
security's annual coupon rate.
Par : is the
security's par value.
(If
you omit par, ACCRINT uses $1,000.)
Frequency : is the
number of coupon payments per year.
(For
annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly,
frequency = 4.)
Basis : is the
type of day count basis to use.
For e.g. see figure
44.
Calculate the payment for a loan
(for e.g. EMI) based on constant payments and constant interest rates?
|
PMT(rate,nper,pv,fv,type) |
Calculates the payment
for a loan (for e.g. EMI) based on constant payments and constant
interest rates. |
Rate : is the
interest rate for the loan.
Nper : is the
total number of payments for the loan.
Pv : is the
present value, or the total amount that a series of future payments is worth
now; also known as the principal.
Fv : is the
future value, or a cash balance you want to attain after the last payment is
made. If fv is omitted, it is assumed to be 0 (zero), that is, the future
value of a loan is 0.
Type : is the
number 0 (zero) or 1 and indicates when payments are due.
For e.g. see figure
Note: you can also use PMT function to
calculate payments to annuities other than bank loans. For e.g. you can
calculate the amount required to be saved per month to accumulate a sum of
amount at the end of a given period where your savings earn interest as well.
45.
Calculate interest rate that I am
actually paying (and the lender is earning) on my home loan or car loan?
(i.e. internal rate of return for a schedule of periodic cash flows)
Use the IRR function to calculate internal
rate of return for a schedule of periodic cash flows. The cash flows do not
have to be even, as they would be for an annuity. However, the cash flows must
occur at regular intervals, such as monthly or annually. The internal rate of
return is the interest rate received for an investment consisting of payments
(negative values) and income (positive values) that occur at regular periods.
For e.g. see figure
46.
Calculate internal rate of return
for a schedule of cash flows that is not necessarily periodic (for e.g. in
case of businesses where cash inflows and outflows are not periodic)?
|
XIRR(values,dates,guess) |
Returns the internal
rate of return for a schedule of cash flows that is not necessarily
periodic |
Values : is a
series of cash flows that corresponds to a schedule of payments in dates. The
first payment is optional and corresponds to a cost or payment that occurs at
the beginning of the investment. If the first value is a cost or payment, it
must be a negative value. All succeeding payments are discounted based on a
365-day year.
47.
Calculate Net Present value for a
schedule of cash flows that is not necessarily periodic?
|
XNPV(rate,values,dates)
|
Calculate Net Present
value for a schedule of cash flows that is not necessarily periodic. |
Rate : is the
discount rate to apply to the cash flows.
Values : is a
series of cash flows that corresponds to a schedule of payments in dates. The
first payment is optional and corresponds to a cost or payment that occurs at
the beginning of the investment. If the first value is a cost or payment, it
must be a negative value. All succeeding payments are discounted based on a
365-day year. The series of values must contain at least one positive value
and one negative value.
Dates : is a
schedule of payment dates that corresponds to the cash flow payments. The
first payment date indicates the beginning of the schedule of payments. All
other dates must be later than this date, but they may occur in any order.
48.
Calculate yield of a bond that pays
periodic interest?
|
YIELD(settlement,maturity,rate,pr,redemption,frequency,basis) |
Calculates yield of a
security that pays periodic interest. |
Important Dates should be entered by using
the DATE function, or as results of other formulas or functions. For example,
use DATE(2008,5,23) for the 23rd day of May, 2008.
Settlement : is the
security's settlement date. The security settlement date is the date after the
issue date when the security is traded to the buyer.
Maturity : is the
security's maturity date. The maturity date is the date when the security
expires.
Rate : is the
security's annual coupon rate.
Pr : is the
security's price per $100 face value.
Redemption : is the
security's redemption value per $100 face value.
Frequency : is the
number of coupon payments per year. For annual payments, frequency=1; for
semiannual, frequency = 2; for quarterly, frequency = 4.
For e.g. see figure
CATEGORY SEVEN – MISCELLANEOUS
Charts and Graphs in Excel:
49.
Which is one of the Best resource on
Excel Charting?
Check out
http://peltiertech.com/. It is one of the best resource on Excel charting
Recovering Corrupted Excel Data:
50.
Recover data from corrupted Excel
file?
If your Excel workbook ever gets corrupted,
you have a very good chance of being able to recover the data by opening the
corrupt workbook in OpenOffice. OpenOffice will NOT recover any VBA code
(macros). Another option is to try to open the spreadsheet data in MS Word,
but it would, most likely, not include formatting and some formulas may be
lost.
In case none of these methods work, you can
try one of the professional data recovery software like http://www.ozgrid.com/Services/corrupt-file-recovery-index.htm.
Macros And VBA:
51.
I have heard a lot about Macros, but
don’t know where to begin with. How do I start with Macros?
The best place to start with Macros and VBA
is to go through Microsoft’s tutorial archived at
http://web.archive.org/web/20031204013634/support.microsoft.com/default.aspx?scid=/support/excel/content/vba101/default.asp
. Also check out this link for explanation on macros, VBA and User defined
Functions:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
